﻿using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Karma.Framework.Core.Domain.Tools.Utils
{
    public class OpenXmlUtils
    {
        public static readonly IList<string> ColumnNames = new List<string>
                                                    {
                                                        "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M",
                                                        "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
                                                        "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK"
                                                        , "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU",
                                                        "AV", "AX", "AY", "AZ",
                                                        "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK"
                                                        , "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU",
                                                        "BV", "BW", "BX", "BY", "BZ",
                                                        "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK"
                                                        , "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU",
                                                        "CV", "CW", "CX", "CY", "CZ",
                                                        "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK"
                                                        , "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU",
                                                        "DV", "DW", "DX", "DY", "DZ",
                                                        "EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK"
                                                        , "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU",
                                                        "EV", "EW", "EX", "EY", "EZ",
                                                        "FA", "FB", "FC", "FD", "FE", "FF", "FG", "FH", "FI", "FJ", "FK"
                                                        , "FL", "FM", "FN", "FO", "FP", "FQ", "FR", "FS", "FT", "FU",
                                                        "FV", "FW", "FX", "FY", "FZ",
                                                        "GA", "GB", "GC", "GD", "GE", "GF", "GG", "GH", "GI", "GJ", "GK"
                                                        , "L", "GM", "GN", "GO", "GP", "GQ", "GR", "GS", "GT", "GU",
                                                        "GV", "GW", "GX", "GY", "GZ",
                                                    };

        public static WorksheetPart AddNewSheet(WorkbookPart workbookPart, string sheetName)
        {
            sheetName = sheetName.Length > 32 ? sheetName.Substring(0, 31) : sheetName;
            var newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            var relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
            var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            if (sheets == null)
            {
                sheets = new Sheets();
                workbookPart.Workbook.AppendChild(sheets);
            }
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }
            var sheet = new Sheet { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();
            return newWorksheetPart;
        }

        public static Cell AddNewCell(WorksheetPart worksheetPart, int columnIndex, uint rowIndex)
        {
            var worksheet = worksheetPart.Worksheet;
            var sheetData = worksheet.GetFirstChild<SheetData>();
            var columnName = ColumnNames[columnIndex];
            var cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            var row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
            if (row == null)
            {
                row = new Row { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            Cell refCell = null;
            foreach (var cell in row.Elements<Cell>())
            {
                var colIdx = ColumnNames.IndexOf(cell.CellReference);
                if (colIdx > columnIndex)
                {
                    refCell = cell;
                    break;
                }
            }
            var newCell = new Cell { CellReference = cellReference };
            row.InsertBefore(newCell, refCell);
            worksheet.Save();
            return newCell;
        }
    }
}
